﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Configuration;
using System.Linq;
using System.Web;

namespace Pr0ject0.Models
{
    public class QuestionAnswerDb
    {
        private string connection;
        List<QuestionModel> questions;
        List<AnswerModel> answers;


        public QuestionAnswerDb()
        {
            connection = "DB";
            update();
        }

        public QuestionAnswerDb(string connectionString)
        {
            connection = connectionString;
            update();
        }
        public void AddQuestion(QuestionModel questions)
        {
            string statement = "INSERT INTO Questions(QuestionID, Question, Type, Title) VALUES((Select Max(QuestionID) From Questions) + 1, '{0}', {1}, {2})";
            string insert; 
            Object[] objects = {questions.question, questions.type, questions.title};

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());
            insert = string.Format(statement, objects);
            SqlCommand cmd = new SqlCommand(insert, conn);

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

        }

        public void AddAnswers(IEnumerable<AnswerModel> answers)
        {
            foreach (AnswerModel a in answers)
            {
                string statement = "INSERT INTO Answers VALUES((Select Max(AnswerID) From Answers) + 1, {0}, '{1}', '{2}')";
                string insert;
                Object[] objects = {a.appId, a.question, a.answer };

                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());
                insert = string.Format(statement, objects);
                SqlCommand cmd = new SqlCommand(insert, conn);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }

        public List<QuestionModel> GetQuestionnaire(int[] titles)
        {
            // should add it for any number that is in the array of integers.
 
        
            List<QuestionModel> _return = questions.FindAll(x => x.title == 0);

            foreach (int i in titles)
            {
                _return.AddRange(questions.FindAll(x => x.title == i));
            }

            return _return;
        }

        public List<AnswerModel> GetAnswers(int appId)
        {
            return answers.FindAll(x => x.appId == appId);
        }

        public void update()
        {
            updateAnswers();
            updateQuestions();
        }

        private void updateQuestions()
        {
            questions = new List<QuestionModel>();
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());
            SqlCommand cmd = new SqlCommand("SELECT * FROM [Questions]", conn);
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                QuestionModel model = new QuestionModel();
                model.id = Convert.ToInt32(reader["QuestionID"].ToString());
                model.question = reader["Question"].ToString();
                model.type = Convert.ToInt32(reader["type"].ToString());
                model.title = Convert.ToInt32(reader["Title"].ToString());
                questions.Add(model);
            }

            reader.Close();
            conn.Close();
        }

        private void updateAnswers()
        {
            answers = new List<AnswerModel>();
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());
            SqlCommand cmd = new SqlCommand("SELECT * FROM [Answers]", conn);
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                AnswerModel model = new AnswerModel();
                model.id = Convert.ToInt32(reader["AnswerID"].ToString());
                model.appId = Convert.ToInt32(reader["ApplicationID"].ToString());
                model.question = reader["Question"].ToString();
                model.answer = reader["Answer"].ToString();
                answers.Add(model);
            }

            reader.Close();
            conn.Close();
        }
    }
}